-- STORE PROCEDURE FOR Country
create proc sp_Get_Countries
as
	begin
		Select * from Countries	
	end
go

create proc sp_Insert_Countries
@CountryName varchar(100)
as
	begin
		Insert into Countries values(@CountryName)
	end
go

create proc sp_Update_Countries
@CountryId int,
@CountryName varchar(100)
as
	begin
		Update Countries set CountryName = @CountryName where CountryId = @CountryId  
	end
go

create proc sp_Delete_Countries
@CountryId int
as
	begin
		Delete from Countries where CountryId = @CountryId
	end
go

create proc sp_GetCountriesLastID
as
	begin
		Select top 1 * from Countries order by CountryId Desc	
	end
go
-----------------------------------------------------------------------------------------------------------------------
-- STORE PROCEDUR FOR Regions
create proc sp_Get_Regions
as
	begin
		Select * from Regions	
	end
go

create proc sp_Insert_Regions
@RegionName varchar(100), 
@CountryId int
as
	begin
		Insert into Regions values(@RegionName, @CountryId)
	end
go

create proc sp_Update_Regions
@RegionId int,
@RegionName varchar(100),
@CountryId int
as
	begin
		Update Regions set RegionName = @RegionName, CountryId = @CountryId where RegionId = @RegionId
	end
go
exec sp_Update_Regions 1,'The North', 2
create proc sp_Delete_Regions
@RegionId int
as
	begin
		Delete from Regions where RegionId = @RegionId
	end
go

create proc sp_GetRegionsLastID
as
	begin
		Select top 1 * from Regions order by RegionId Desc	
	end
go

create proc sp_Get_Regions_With_Country_Name
as
	begin
		Select Regions.RegionId, Regions.RegionName, Countries.CountryName
		from Regions join Countries on Regions.CountryId = Countries.CountryId 	
	end
go

create proc sp_Get_Regions_By_CountryId
@CountryId int
as
	begin
		Select * from Regions where CountryId = @CountryId
	end
go

create proc sp_deleteCountry
@CountryId as int
AS
DELETE FROM Countries WHERE CountryId = @CountryId


exec sp_Get_Regions_With_Country_Name
-----------------------------------------------------------------------------------------------------------------------
-- STORE PROCEDUR FOR Cities
create proc sp_Get_Cities
as
	begin
		Select * from Cities
	end
go

create proc sp_Insert_Cities
@CityId int,
@CityName varchar(100),
@RegionId int
as
	begin
		Insert into Cities values(@CityName, @RegionId)
	end
go

create proc sp_Update_Cities
@CityId int,
@CityName varchar(100),
@RegionId int
as
	begin
		Update Cities set CityName = @CityName, RegionId = @RegionId where CityId = @CityId 
	end
go

create proc sp_Delete_Cities
@CityId int
as
	begin
		Delete from Cities where CityId = @CityId
	end
go

create proc sp_Get_Cities_With_Region_Name
as
	begin
		Select Cities.CityId, Cities.CityName, Regions.RegionName
		from Cities join Regions on Cities.RegionId = Regions.RegionId	
	end
go

create proc sp_GetCitiesLastID
as
	begin
		Select top 1 * from Cities order by CityId Desc	
	end
go
-----------------------------------------------------------------------------------------------------------------------

-- STORE PROCEDUR FOR Districts
create proc sp_Get_Districts
as
	begin
		Select * from Districts	
	end
go

create proc sp_Insert_Districts
@DistrictName varchar(100),
@CityId int
as
	begin
		Insert into Districts values(@DistrictName, @CityId)
	end
go

create proc sp_Update_Districts
@DistrictId int,
@DistrictName varchar(100),
@CityId int
as
	begin
		Update Districts set DistrictName = @DistrictName, CityId = @CityId where DistrictId = @DistrictId
	end
go


create proc sp_Delete_Districts
@DistrictId int
as
	begin
		Delete from Districts where DistrictId = @DistrictId
	end
go

create proc sp_Get_Districts_With_City_Name
as
	begin
		Select Districts.DistrictId, Districts.DistrictName, Cities.CityName
		from Districts join Cities on Districts.CityId = Cities.CityId	
	end
go

create proc sp_GetDistrictsLastID
as
	begin
		Select top 1 * from Districts order by DistrictId Desc	
	end
go
-----------------------------------------------------------------------------------------------------------------------
-- STORE PROCEDURE FOR ADVERTISEMENT
Create proc sp_Insert_Advertisement
@CategoryId int,
@UserId int,
@DistrictId int, 
@PackageId int, 
@Title varchar(250), 
@Type varchar(50),
@ImageUrl varchar(MAX),
@Price money,
@PostedDate varchar(50),
@ExpDate varchar(50),
@LandArea float,
@Floor int,
@BedRoom int,
@BathRoom int, 
@BuiltYear int, 
@Address varchar(MAX),
@Description text,
@Amenities varchar(MAX),
@IsFeatured bit,
@IsActive bit
as
	begin
		Insert into Advertisements values(@CategoryId, @UserId, @DistrictId, @PackageId, @Title, @Type,@ImageUrl,
											@Price, @PostedDate, @ExpDate, @LandArea, @Floor, @BedRoom, @BathRoom, @BuiltYear, 
											@Address, @Description, @Amenities, @IsFeatured, @IsActive)
	end
go

create proc sp_Update_Advertisements
@AdvId int,
@CategoryId int,
@UserId int,
@DistrictId int, 
@PackageId int, 
@Title varchar(250), 
@Type varchar(50),
@ImageUrl varchar(MAX),
@Price money,
@PostedDate varchar(50),
@ExpDate varchar(50),
@LandArea float,
@Floor int,
@BedRoom int,
@BathRoom int, 
@BuiltYear int, 
@Address varchar(MAX),
@Description text,
@Amenities varchar(MAX),
@IsFeatured bit,
@IsActive bit
as
	begin
		Update Advertisements set CategoryId = @CategoryId, UserId = @UserId, DistrictId = @DistrictId, PackageId = @PackageId, Title = @Title, Type = @Type, ImageUrl = @ImageUrl,
									Price = @Price, PostedDate = @PostedDate, ExpDate= @ExpDate, LandArea = @LandArea, Floor = @Floor, BedRoom = @BedRoom, BathRoom = @BathRoom, 
									BuiltYear = @BuiltYear, Address = @Address, Description = @Description, Amenities = @Amenities, IsFeatured = @IsFeatured, IsActive = @IsActive
							where AdvId = @AdvId
	end
go

create proc sp_Delete_Advertisements
@AdvId int
as
	begin
		Delete from Advertisements where AdvId = @AdvId
	end
go

create proc sp_GetAdvertisementsLastID
as
	begin
		Select top 1 * from Advertisements order by AdvId Desc	
	end
go

create proc sp_Get_AdvId_By_UserId
@UserId int
as
	begin
		Select * from Advertisements where UserId = @UserId
	end
go

create proc sp_Get_Advertisement
as
	begin
		Select * from Advertisements
	end
go

create proc sp_Get_Advertisements_Details
@AdvId int
as
	begin 
		select	Districts.DistrictName, Cities.CityName, Regions.RegionName, Countries.CountryName,
				Packages.PackageName, Advertisements.Title, Advertisements.Type, Advertisements.ImageUrl, Advertisements.Price, 
				Advertisements.PostedDate, Advertisements.ExpDate, Advertisements.LandArea, Advertisements.Floor,
				Advertisements.BedRoom, Advertisements.BathRoom, Advertisements.BuiltYear, Advertisements.Address,
				Advertisements.Description, Advertisements.Amenities, Advertisements.IsFeatured, Advertisements.IsActive,
				Categories.CategoryName, Packages.PackageName, Users.FirstName, Users.LastName 
				from Advertisements join Categories on Categories.CategoryId = Advertisements.CategoryId
						join Packages on Packages.PackageId =Advertisements.PackageId
						join Users on Users.UserId = Advertisements.UserId
						join Districts on Districts.DistrictId = Advertisements.DistrictId
						join Cities on Cities.CityId= Districts.DistrictId
						join Regions on Regions.RegionId = Cities.RegionId
						join Countries on Countries.CountryId = Regions.CountryId
		where Advertisements.AdvId = @AdvId
	end
go


create proc sp_Get_Many_Table_With_Advertisements
@UserId int
as
	begin
	select Categories.CategoryName, Districts.DistrictName, Cities.CityName, Regions.RegionName, Countries.CountryName,
			Packages.PackageName, Advertisements.AdvId, Advertisements.Title, Advertisements.Type, Advertisements.ImageUrl, Advertisements.Price, 
			Advertisements.PostedDate, Advertisements.ExpDate, Advertisements.LandArea, Advertisements.Floor,
			Advertisements.BedRoom, Advertisements.BathRoom, Advertisements.BuiltYear, Advertisements.Address,
			Advertisements.Description, Advertisements.Amenities, Advertisements.IsFeatured, Advertisements.IsActive
			--AdsImages.ImageUrl 
		from Advertisements join Categories on Categories.CategoryId = Advertisements.CategoryId
						join Packages on Packages.PackageId =Advertisements.PackageId
						join Districts on Districts.DistrictId = Advertisements.DistrictId
						join Cities on Cities.CityId= Districts.DistrictId
						join Regions on Regions.RegionId = Cities.RegionId
						join Countries on Countries.CountryId = Regions.CountryId
						--join AdsImages on AdsImages.AdvId = Advertisements.AdvId
			where UserId = @UserId 
	end
go

create proc sp_Get_Many_Table_With_CategoryId
@CategoryId int
as
	begin
	select Categories.CategoryName, Districts.DistrictName, Cities.CityName, Regions.RegionName, Countries.CountryName,
			Packages.PackageName, Advertisements.AdvId, Advertisements.Title, Advertisements.Type, Advertisements.ImageUrl, Advertisements.Price, 
			Advertisements.PostedDate, Advertisements.ExpDate, Advertisements.LandArea, Advertisements.Floor,
			Advertisements.BedRoom, Advertisements.BathRoom, Advertisements.BuiltYear, Advertisements.Address,
			Advertisements.Description, Advertisements.Amenities, Advertisements.IsFeatured, Advertisements.IsActive
			--AdsImages.ImageUrl 
		from Advertisements join Categories on Categories.CategoryId = Advertisements.CategoryId
						join Packages on Packages.PackageId =Advertisements.PackageId
						join Districts on Districts.DistrictId = Advertisements.DistrictId
						join Cities on Cities.CityId= Districts.DistrictId
						join Regions on Regions.RegionId = Cities.RegionId
						join Countries on Countries.CountryId = Regions.CountryId
						--join AdsImages on AdsImages.AdvId = Advertisements.AdvId
			where Advertisements.CategoryId = @CategoryId
	end
go

create proc sp_Get_Many_Table_With_Title
@Title varchar(250)
as
	begin
	select Categories.CategoryName, Districts.DistrictName, Cities.CityName, Regions.RegionName, Countries.CountryName,
			Packages.PackageName, Advertisements.AdvId, Advertisements.Title, Advertisements.Type, Advertisements.ImageUrl, Advertisements.Price, 
			Advertisements.PostedDate, Advertisements.ExpDate, Advertisements.LandArea, Advertisements.Floor,
			Advertisements.BedRoom, Advertisements.BathRoom, Advertisements.BuiltYear, Advertisements.Address,
			Advertisements.Description, Advertisements.Amenities, Advertisements.IsFeatured, Advertisements.IsActive
			--AdsImages.ImageUrl 
		from Advertisements join Categories on Categories.CategoryId = Advertisements.CategoryId
						join Packages on Packages.PackageId =Advertisements.PackageId
						join Districts on Districts.DistrictId = Advertisements.DistrictId
						join Cities on Cities.CityId= Districts.DistrictId
						join Regions on Regions.RegionId = Cities.RegionId
						join Countries on Countries.CountryId = Regions.CountryId
						--join AdsImages on AdsImages.AdvId = Advertisements.AdvId
			where Title = @Title
	end
go


create proc sp_Get_ImageUrl_By_Id
@AdvId int
as
	begin
		Select * from Advertisements where AdvId=@AdvId
	end
go

create proc sp_UpdateDuration
@AdvId int,
@ExpDate varchar(50)
as
	begin
		Update Advertisements set ExpDate = @ExpDate where AdvId=@AdvId
	end
go

create proc sp_GetAdByCateId
@CategoryId int
as
	begin
		Select * from Advertisements where CategoryId = @CategoryId
	end
go

-----------------------------------------------------------------------------------------------------------------------
------------Add New
create proc sp_Get_By_CountryName
@CountryName varchar(100)
as
	begin
		Select * from Countries where CountryName = @CountryName	
	end
go


create proc sp_Search_By_Region
@RegionName varchar(100)
as
	begin
		Select * from Regions where RegionName = @RegionName	
	end
go

create proc sp_Search_By_City
@CityName varchar(100)
as
	begin
		Select * from Cities where CityName = @CityName	
	end
go

create proc sp_Search_By_District
@DistrictName varchar(100)
as
	begin
		Select * from Districts where DistrictName = @DistrictName	
	end
go

create proc sp_Get_All_By_Countries
@CountryName varchar(100)
as
	begin
		select Countries.CountryId, Countries.CountryName, Regions.RegionName, Cities.CityName, Districts.DistrictName
		from Countries join Regions on Countries.CountryId = Regions.CountryId
		join Cities on Cities.RegionId = Regions.RegionId
		join Districts on Districts.CityId = Cities.CityId
		where CountryName = @CountryName	
	end
go

drop proc sp_Search_All_By_Districts

exec sp_Search_All_By_Countries 'VietNam'

create proc sp_Get_All_By_Regions
@RegionName varchar(100)
as
	begin
		Select Countries.CountryId, Countries.CountryName, Regions.RegionName, Cities.CityName, Districts.DistrictName
			from Countries join Regions on Countries.CountryId = Regions.CountryId
			join Cities on Cities.RegionId = Regions.RegionId
			join Districts on Districts.CityId = Cities.CityId
			where RegionName = @RegionName
	end
go

create proc sp_Get_All_By_Cities
@CityName varchar(100)
as
	begin
		Select Countries.CountryId, Countries.CountryName, Regions.RegionName, Cities.CityName, Districts.DistrictName
			from Countries join Regions on Countries.CountryId = Regions.CountryId
			join Cities on Cities.RegionId = Regions.RegionId
			join Districts on Districts.CityId = Cities.CityId
			where CityName = @CityName
	end
go

create proc sp_Get_All_By_Districts
@DistrictName varchar(100)
as
	begin
		Select Countries.CountryId, Countries.CountryName, Regions.RegionName, Cities.CityName, Districts.DistrictName
			from Countries join Regions on Countries.CountryId = Regions.CountryId
			join Cities on Cities.RegionId = Regions.RegionId
			join Districts on Districts.CityId = Cities.CityId
			where DistrictName = @DistrictName
	end
go

create proc sp_Get_Countries_By_Id
@CountryId int
as
	begin
		Select * from Countries where CountryId = @CountryId
	end
go

create proc sp_Get_Regions_By_Id
@RegionId int
as
	begin
		Select * from Regions where RegionId = @RegionId
	end
go


create proc sp_Get_City_By_Id
@CityId int
as
	begin
		Select * from Cities where CityId = @CityId
	end
go

create proc sp_Get_City_By_RegionId
@RegionId int
as
	begin
		Select * from Cities where RegionId = @RegionId
	end
go


create proc sp_Get_District_By_Id
@DistrictId int
as
	begin
		Select * from Districts where DistrictId = @DistrictId
	end
go

create proc sp_Get_District_By_CityId
@CityId int
as
	begin
		Select * from Districts where CityId = @CityId
	end
go

-------------------------------------------------------------------------------------
------------------------PACKAGES-----------------------
create proc sp_Get_Parkages
as
	begin
		Select * from Packages
	end
go

create proc sp_Get_PackagNameByAdvId
@AdvId int
as
	begin
	select Packages.PackageName	
		from Advertisements join Packages on Packages.PackageId =Advertisements.PackageId
			where AdvId = @AdvId 
	end
go


create proc sp_Get_PackageId_By_AdvId
@AdvId int
as
	begin
	select * from Advertisements where AdvId = @AdvId 
	end
go
------------------------CATEGORIES-----------------------
create proc sp_Get_Categories
as
	begin
		Select * from Categories
	end
go

create proc sp_Get_CategoryById
@CategoryId int
as
	begin
		Select * from Categories where CategoryId = @CategoryId
	end
go

create proc sp_Get_CategoryByName
@CategoryName varchar(200)
as 
	begin
		Select * from Categories where CategoryName=@CategoryName
	end
go
----------------------------PACKAGES----------------------
create proc sp_Get_Packages_By_Id
@PackageId int
as
	begin
		Select * from Packages where PackageId =@PackageId
	end
go

create proc sp_Get_Package_By_Name
@PackageName varchar(100)
as
	begin
		Select * from Packages where PackageName = @PackageName
	end
go


--------------------------ADSIMAGES------------------------
create proc sp_Insert_AdsImages
@AdvId int,
@ImageUrl varchar(200)  
as
	begin
		Insert into AdsImages values(@AdvId, @ImageUrl) 
	end
go

create proc sp_Delete_AdsImages
@AdvId int 
as
	begin
		Delete from AdsImages where AdvId = @AdvId
	end
go

create proc sp_Update_AdsImages
@Id int,
@AdvId int,
@ImageUrl varchar(200)
as
	begin
		Update AdsImages set ImageUrl=@ImageUrl where AdvId = @AdvId and Id = @Id
	end
go

create proc sp_Get_ImageUrl_ByAdvId
@AdvId int
as
	begin
		Select * from AdsImages where AdvId=@AdvId
	end
go

create proc sp_Delete_AdsImages_ById
@Id int
as
	begin
		Delete from AdsImages where Id = @Id
	end
go

----------------------------------------------------------------------------------------
create proc sp_GetUserId
@UserId varchar(50)
as
	begin
		Select * from Users where UserId = @UserId
	end
go

----------------------------------------------------------------------------------------
create proc sp_GetAdminId
@AdminId varchar(50)
as
	begin
		Select * from Administrators where AdminId = @AdminId
	end
go

-------------------------------------SUBSCRIPTION-------------------------------
create proc sp_InsertIntoSubscription
@SubDate varchar(50),
@UserId int,
@PackageId int,
@IsPaid bit
as
	begin
		Insert into Subscription values(@SubDate, @UserId, @PackageId, @IsPaid)
	end
go